package Server.Database;

import java.sql.Statement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.util.Vector;

import Server.Medida;
import Server.Sensor;

public class Conexion {

	private static Connection connection;
	private static ResultSet resultSet;
	private static Statement statement;
	
	public Conexion(){
		
		 connection = null;  
	     resultSet = null;  
	     statement = null;  
	     
	}
	
	//Devuelve un vector de sensores para el comando LISTSENSOR
	public static Vector<Sensor> listaSensores(){
		Vector<Sensor> s=new Vector<Sensor>();
		
		
		try 
	     {  
	         Class.forName("org.sqlite.JDBC");  
	         connection = DriverManager.getConnection("jdbc:sqlite:Resources/ServerDB.sqlite");
	      //   System.out.println("conectado");
	         statement = connection.createStatement();  
	         resultSet = statement  
	                 .executeQuery("SELECT * FROM sensor");  
	         while (resultSet.next()) 
	         {  
	             Sensor sensor=new Sensor(resultSet.getInt(1),resultSet.getString(2),resultSet.getBoolean(3));
	        	 s.add(sensor);
	         }  
	     } 
	     catch (Exception e) 
	     {  
	         e.printStackTrace();  
	     }
	     finally 
	     {  
	         try 
	         {  
	             resultSet.close();  
	             statement.close();  
	             connection.close();
	           //  System.out.println("Conexion finalizada");
	         } 
	         catch (Exception e) 
	         {  
	             e.printStackTrace();  
	         }  
	     } 
		return s;
	}
	
	//Devuelve un vector de medidas para el comando HISTORICO
	public static Vector<Medida> listaMedidas(int id){
		Vector<Medida> v=new Vector<Medida>();
		try 
	     {  
	         Class.forName("org.sqlite.JDBC");  
	         connection = DriverManager.getConnection("jdbc:sqlite:Resources/ServerDB.sqlite");
	      //   System.out.println("conectado");
	         statement = connection.createStatement();  
	         resultSet = statement  
	                 .executeQuery("SELECT * FROM medida WHERE id_sensor="+id);  
	         while (resultSet.next()) 
	         {  
	            Medida m=new Medida(resultSet.getDate(3),resultSet.getTime(4),0,0,resultSet.getFloat(6));
	        	int id_coord=resultSet.getInt(5);
	            Statement st2=connection.createStatement();
	        	ResultSet rs2=st2.executeQuery("SELECT * FROM coordenada WHERE id_coord="+id_coord);
	        	while(rs2.next()){
	        		float latitud=rs2.getFloat(2);
	        		float longitud=rs2.getFloat(3);
	        		m.setCoordenadas(latitud, longitud);
	        	}
	        	rs2.close();
	        	st2.close();
	            v.add(m);
	         }  
	     } 
	     catch (Exception e) 
	     {  
	         e.printStackTrace();  
	     }
	     finally 
	     {  
	         try 
	         {  
	        	 
	             resultSet.close();  
	             statement.close();  
	             connection.close();
	           //  System.out.println("Conexion finalizada");
	         } 
	         catch (Exception e) 
	         {  
	             e.printStackTrace();  
	         }  
	     } 
		return v;
	}
	
	//Cambia el estado del sensor a ON si su estado previo es OFF
	//Devolvera 1 si se ha cambiado correctamente a ON
	//Devolvera 0 si su estado previo era ON
	//Devolvera -1 si el sensor no existe
	public static int setSensorON(int id_sensor){
		int i=2;
		boolean b=false;
		try 
	     {  
	         Class.forName("org.sqlite.JDBC");  
	         connection = DriverManager.getConnection("jdbc:sqlite:Resources/ServerDB.sqlite");
	      //   System.out.println("conectado");
	         statement = connection.createStatement();  
	         resultSet = statement  
	                 .executeQuery("SELECT estado FROM sensor WHERE id_sensor="+id_sensor);  
	        if(resultSet.getRow()==0){
	        	i=-1;
	        }else{
	         while (resultSet.next()) 
	         {  
	            b=resultSet.getBoolean(1);
	         }  
	         if(b==false){
	        	 i=1;
	        	 statement.executeQuery("UPDATE sensor SET estado = 'true' WHERE id_sensor="+id_sensor);  
	         }else{
	        	 i=-1;
	         }
	        }
	     } 
	     catch (Exception e) 
	     {  
	         e.printStackTrace();  
	     }
	     finally 
	     {  
	         try 
	         {  
	             resultSet.close();  
	             statement.close();  
	             connection.close();
	           //  System.out.println("Conexion finalizada");
	         } 
	         catch (Exception e) 
	         {  
	             e.printStackTrace();  
	         }  
	     } 
		return i;
	}
	
	    //Cambia el estado del sensor a OFF si su estado previo es ON
		//Devolvera 1 si se ha cambiado correctamente a OFF
		//Devolvera 0 si su estado previo era OFF
	    //Devolvera -1 si el sensor no existe
		public static int setSensorOFF(int id_sensor){
			int i=2;
			boolean b=false;
			try 
		     {  
		         Class.forName("org.sqlite.JDBC");  
		         connection = DriverManager.getConnection("jdbc:sqlite:Resources/ServerDB.sqlite");
		      //   System.out.println("conectado");
		         statement = connection.createStatement();  
		         resultSet = statement  
		                 .executeQuery("SELECT estado FROM sensor WHERE id_sensor="+id_sensor);  
		         if(resultSet.getRow()==0){
			        	i=-1;
			        }else{
		          
		            b=resultSet.getBoolean(1);
		         
			        
		         if(b==true){
		        	 i=1;
		        	 statement.executeQuery("UPDATE sensor SET estado = 'false' WHERE id_sensor="+id_sensor);  
		         }else{
		        	 i=0;
		         }
			        }
		     } 
		     catch (Exception e) 
		     {  
		         e.printStackTrace();  
		     }
		     finally 
		     {  
		         try 
		         {  
		             resultSet.close();  
		             statement.close();  
		             connection.close();
		           //  System.out.println("Conexion finalizada");
		         } 
		         catch (Exception e) 
		         {  
		             e.printStackTrace();  
		         }  
		     } 
			return i;
		}
		
		 //Cambia el estado del GPS a ON si su estado previo es OFF
		//Devolvera true si se ha cambiado correctamente a ON
		//Devolvera false si su estado previo era ON
		public static boolean setGPSON(String iD_Vehiculo){
			boolean b=false;
			try 
		     {  
		         Class.forName("org.sqlite.JDBC");  
		         connection = DriverManager.getConnection("jdbc:sqlite:Resources/ServerDB.sqlite");
		      //   System.out.println("conectado");
		         statement = connection.createStatement();  
		         resultSet = statement  
		                 .executeQuery("SELECT estado_gps FROM vehiculo WHERE id_vehiculo="+iD_Vehiculo);  
		          
		            b=resultSet.getBoolean(1);
		           
		         if(b==false){
		        	 statement.executeQuery("UPDATE vehiculo SET estado_gps = 'true' WHERE id_vehiculo="+iD_Vehiculo);  
		         }
		     } 
		     catch (Exception e) 
		     {  
		         e.printStackTrace();  
		     }
		     finally 
		     {  
		         try 
		         {  
		             resultSet.close();  
		             statement.close();  
		             connection.close();
		           //  System.out.println("Conexion finalizada");
		         } 
		         catch (Exception e) 
		         {  
		             e.printStackTrace();  
		         }  
		     } 
			return b;
		}
	
		 //Cambia el estado del sensor a OFF si su estado previo es ON
		//Devolvera true si se ha cambiado correctamente a OFF
		//Devolvera false si su estado previo era OFF
		public static boolean setGPSOFF(String iD_Vehiculo){
			boolean b=false;
			try 
		     {  
		         Class.forName("org.sqlite.JDBC");  
		         connection = DriverManager.getConnection("jdbc:sqlite:Resources/ServerDB.sqlite");
		      //   System.out.println("conectado");
		         statement = connection.createStatement();  
		         resultSet = statement  
		                 .executeQuery("SELECT estado_gps FROM vehiculo WHERE id_vehiculo="+iD_Vehiculo);  
		        
		            b=resultSet.getBoolean(1);
	  
		         if(b==true){
		        	 statement.executeQuery("UPDATE vehiculo SET estado_gps = 'false' WHERE id_vehiculo="+iD_Vehiculo);  
		         }
		     } 
		     catch (Exception e) 
		     {  
		         e.printStackTrace();  
		     }
		     finally 
		     {  
		         try 
		         {  
		             resultSet.close();  
		             statement.close();  
		             connection.close();
		           //  System.out.println("Conexion finalizada");
		         } 
		         catch (Exception e) 
		         {  
		             e.printStackTrace();  
		         }  
		     } 
			return b;
		}
		
		//Devolvera la lista de atributos para el comando GET_VALACT is_sensor
		//Devolvera el objeto Medida correspondiente si el comando se ha ejecutado correctamente
		//Devolvera 0 si el sensor esta a off
		//Devolvera -1 si no existe el sensor introducido
		public static Object getValact(int id_sensor){
			Object o=new Object();
			Vector<Integer> a=new Vector<Integer>();
			try 
		     {  
		         Class.forName("org.sqlite.JDBC");  
		         connection = DriverManager.getConnection("jdbc:sqlite:Resources/ServerDB.sqlite");
		      //   System.out.println("conectado");
		         statement = connection.createStatement();  
		         resultSet = statement  
		                 .executeQuery("SELECT id_medida FROM medida WHERE id_sensor="+id_sensor);  
		         while (resultSet.next()) 
		         {  
		            int ar=resultSet.getInt(1);
		            a.add(ar);
		        	
		         }  
		         ResultSet rs = statement  
		                 .executeQuery("SELECT * FROM medida WHERE id_medida="+a.lastElement()); 
		            Medida m=new Medida(rs.getDate(3), rs.getTime(4), 0, 0, rs.getFloat(6));
		            int id_coord=rs.getInt(5);
		            ResultSet rS = statement  
			                 .executeQuery("SELECT * FROM coordenadas WHERE id_coord="+id_coord); 	
		            m.setCoordenadas(rS.getFloat(2), rS.getFloat(3));
		            if(!resultSet.last()){
		            	o=-1;
		            }else{
		            ResultSet r=statement  
			                 .executeQuery("SELECT * FROM sensor WHERE id_sensor="+id_sensor); 
		            if(r.getBoolean(3)==false){
		            	o=0;
		            }else{
		            	o=m;
		            }
		            }
		     } 
		     catch (Exception e) 
		     {  
		         e.printStackTrace();  
		     }
		     finally 
		     {  
		         try 
		         {  
		             resultSet.close();  
		             statement.close();  
		             connection.close();
		           //  System.out.println("Conexion finalizada");
		         } 
		         catch (Exception e) 
		         {  
		             e.printStackTrace();  
		         }  
		     }
			return o;
		}
		
		
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub

	}

}

